﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Dapper.Contrib.Extensions;

namespace Topro.Application.Impl
{
    using Topro.Application.Interface;
    using Topro.Db;
    using Topro.Entity;
    using Topro.Entity.Enum;
    using Topro.Entity.Query;
    using Topro.Entity.Request.WarehouseUnit;
    using Topro.Entity.Response.WarehouseUnit;
    using Topro.Entity.Tables;
    using Topro.Log;

    /// <summary>
    /// 库位业务接口实现
    /// </summary>
    public class WarehouseUnitServiceImpl: WarehouseUnitService
    {
        #region 注入
        /// <summary>
        /// db
        /// </summary>
        private readonly IDb _db;

        /// <summary>
        /// loggerService
        /// </summary>
        private readonly LoggerService _loggerService;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dapperFactory"></param>
        /// <param name="logger"></param>
        public WarehouseUnitServiceImpl(IDb db, LoggerService loggerService)
        {
            _db = db;
            _loggerService = loggerService;
        }
        #endregion

        /// <summary>
        /// 获取库位
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public WarehouseUnitResponse Get(string id)
        {
            var warehouseUnit = new WarehouseUnitResponse();

            if (!string.IsNullOrEmpty(id))
            {
                warehouseUnit = _db.DbConnection.QueryFirst<WarehouseUnitResponse>("select * from t_WarehouseUnit where Id=@Id", new { Id = id });
            }
            return warehouseUnit;
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        /// <returns></returns>
        public JResult UpdateStatus(string id, int status)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<WarehouseUnitResponse>("select * from t_WarehouseUnit where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("物料记录不存在.");
                }

                var updateSql = "UPDATE t_WarehouseUnit SET Status=@Status WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { Status = status, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 保存、修改
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult SaveWarehouseUnit(WarehouseUnitSaveRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            if (!string.IsNullOrEmpty(request.Id))
            {
                var warehouseNameExist = _db.DbConnection.Query<WarehouseUnit>("SELECT * FROM t_WarehouseUnit WHERE WarehouseId=@WarehouseId And Type=@Type And Pai=@Pai And Lie=@Lie And Ceng=@Ceng And Id!=@Id", new { WarehouseId = request.WarehouseId.Value, Type = request.Type.Value, Pai = request.Pai, Lie = request.Lie, Ceng = request.Ceng, request.Id });
                if (warehouseNameExist != null && warehouseNameExist.Any())
                {
                    throw new Exception("库位名称已存在.");
                }

                var warehouse = _db.DbConnection.QueryFirst<WarehouseUnit>("select * from t_WarehouseUnit where Id=@Id", new { Id = request.Id });
                if (warehouse == null)
                {
                    throw new Exception("库位记录不存在.");
                }

                warehouse.WarehouseId = request.WarehouseId.Value;
                warehouse.Type = request.Type.Value;
                warehouse.Pai = int.Parse(request.Pai);
                warehouse.Lie = int.Parse(request.Lie);
                warehouse.Ceng = int.Parse(request.Ceng);
                //warehouse.Locked
                warehouse.Status = request.Status.Value;
                warehouse.Remark = request.Remark;
                //product.ContainerCode

                //更新
                _db.DbConnection.Update<WarehouseUnit>(warehouse);
                //
                response.Success = true;
            }
            else
            {
                var warehouseNameExist = _db.DbConnection.Query<WarehouseUnit>("SELECT * FROM t_WarehouseUnit WHERE WarehouseId=@WarehouseId And Type=@Type And Pai=@Pai And Lie=@Lie And Ceng=@Ceng", new { WarehouseId = request.WarehouseId.Value, Type = request.Type.Value, Pai = request.Pai, Lie = request.Lie, Ceng = request.Ceng });
                if (warehouseNameExist != null && warehouseNameExist.Any())
                {
                    throw new Exception("库位已存在.");
                }

                var warehouse = new WarehouseUnit()
                {
                    WarehouseId = request.WarehouseId.Value,
                    Type = request.Type.Value,
                    Pai = int.Parse(request.Pai),
                    Lie = int.Parse(request.Lie),
                    Ceng = int.Parse(request.Ceng),
                    //Locked = (int)WarehouseLocked.否,
                    Status = request.Status.Value,
                    Remark = request.Remark,
                    ContainerCode = null,
                    CreatedBy = request.CreatedBy,
                    CreateTime = DateTime.Now
                };

                //新增
                var resultId = _db.DbConnection.Insert<WarehouseUnit>(warehouse);
                if (resultId <= 0)
                {
                    throw new Exception("新增库位失败.");
                }
                response.Success = true;
            }
            return response;
        }

        /// <summary>
        /// 解锁
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public JResult Locked(string id)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<WarehouseUnitResponse>("select * from t_WarehouseUnit where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("库位记录不存在.");
                }

                var updateSql = "UPDATE t_WarehouseUnit SET Locked=@Locked WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { Locked = (int)WarehouseLocked.否, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 解锁
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public JResult OutLocked(string id)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var user = _db.DbConnection.QueryFirst<WarehouseUnitResponse>("select * from t_WarehouseUnit where Id=@Id", new { Id = id });
                if (user == null)
                {
                    throw new Exception("库位记录不存在.");
                }

                var updateSql = "UPDATE t_WarehouseUnit SET OutLocked=@Locked,ContainerCode='' WHERE Id=@Id";

                int result = _db.DbConnection.Execute(updateSql, new { Locked = (int)WarehouseLocked.否, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }
                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<WarehouseUnitQueryResponse> GetPager(WarehouseUnitQuery query)
        {
            var pageIndex = query.PageIndex <= 0 ? 0 : (query.PageIndex - 1) * query.PageSize;

            var files = "wu.Id,w.Name AS WarehouseName,wu.Type,wu.Code,wu.Pai,wu.Lie,wu.Ceng,wu.Locked,wu.ContainerCode,wu.IsContainer,wu.Status,wu.Remark,wu.CreatedBy,wu.CreateTime";
            var tableName = "t_WarehouseUnit wu LEFT JOIN t_warehouse w ON wu.WarehouseId=w.Id";
            var orderby = "ORDER BY wu.Pai,wu.Lie,wu.Ceng ASC";
            var where = "where 1=1";

            #region 条件
            if (query.Pai.HasValue)
                where += string.Format(" And wu.Pai ={0}", query.Pai.Value);
            if (query.Lie.HasValue)
                where += string.Format(" And wu.Lie ={0}", query.Lie.Value);
            if (query.Ceng.HasValue)
                where += string.Format(" And wu.Ceng ={0}", query.Ceng.Value);
            if (query.Locked.HasValue)
                where += string.Format(" And wu.Locked ={0}", query.Locked.Value);
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.PageSize);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, query.Start, query.Rows);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<WarehouseUnitQueryResponse>();

                return new DataGrid<WarehouseUnitQueryResponse>()
                {
                    Rows = list,
                    Total = count
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<WarehouseUnitQueryResponse> WarehouseUnitQuery(WarehouseUnitQuery query)
        {
            var files = "Id,Type,Pai,Lie,Ceng,Locked,Status,Remark,CreatedBy,CreateTime";
            var tableName = "t_warehouseunit";
            var orderby = "ORDER BY Pai,Lie,Ceng ASC";
            var where = "where 1=1";

            #region 条件
            if (query.Pai.HasValue)
            {
                where += string.Format(" And Pai ={0}", query.Pai.Value);
            }
            if (query.Lie.HasValue)
            {
                where += string.Format(" And Lie ={0}", query.Lie.Value);
            }
            if (query.Ceng.HasValue)
            {
                where += string.Format(" And Ceng ={0}", query.Ceng.Value);
            }
            if(query.Locked.HasValue)
            {
                where += string.Format(" And Locked ={0}", query.Locked.Value);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            //sb.AppendFormat("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only", files, tableName, where, orderby, pageIndex, query.PageSize);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, query.Start, query.Rows);
            using (var multi = _db.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<WarehouseUnitQueryResponse>();

                return new PagedList<WarehouseUnitQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.PageIndex,
                    TotalCount = count
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<WarehouseUnitQueryResponse> Query(WarehouseUnitQuery query)
        {
            var tableName = @"SELECT 
wu.*
FROM 
t_warehouseunit wu
";

            var orderby = "ORDER BY wu.Pai,wu.Lie,wu.Ceng ASC";

            var where = "WHERE wu.Status=1 and wu.Locked =0 and wu.id NOT IN(SELECT i.WarehouseUnitId FROM t_inventory i WHERE i.Status=2 and i.InventoryQty>0) ";

            #region 条件
            if (query.Type.HasValue)
            {
                where += string.Format(" And Type ={0}", query.Type.Value);
            }
            if (query.Pai.HasValue)
            {
                where += string.Format(" And Pai ={0}", query.Pai.Value);
            }
            if (query.Lie.HasValue)
            {
                where += string.Format(" And Lie ={0}", query.Lie.Value);
            }
            if (query.Ceng.HasValue)
            {
                where += string.Format(" And Ceng ={0}", query.Ceng.Value);
            }
            if (query.Locked.HasValue)
            {
                where += string.Format(" And Locked ={0}", query.Locked.Value);
            }
            #endregion

            var sb = string.Format("{0} {1} {2};", tableName, where, orderby);

            var rows = _db.DbConnection.Query<WarehouseUnitQueryResponse>(sb);

            return new DataGrid<WarehouseUnitQueryResponse>()
            {
                Rows = rows,
                Total = 0
            };
        }
    }
}
